from sqlalchemy import String, Column, Float, Text, BigInteger, Integer, DateTime, event
from sqlalchemy.orm import Session
from sqlalchemy.sql.operators import is_

from config.database import Base


class PurchaseDailyPrice(Base):
    """
    每日采购价记录表
    """

    __tablename__ = 'purchase_daily_price'

    purchase_price_id = Column(Integer, primary_key=True, autoincrement=True, nullable=False, comment='每日收购单价记录ID')
    year = Column(Integer, nullable=True, comment='数据年份')
    create_by = Column(String(255), nullable=True, comment='发布人')
    create_time = Column(DateTime, nullable=True, comment='发布时间')
    price = Column(Float, nullable=True, comment='收购单价')
    purchase_date = Column(DateTime, nullable=True, comment='收购日期')
    effective_time = Column(BigInteger, nullable=True, comment='生效时间')
    expiration_time= Column(BigInteger, nullable=True, comment='失效时间')
    update_by = Column(String(255), nullable=True, comment='更新人')
    update_time = Column(DateTime, nullable=True, comment='更新时间')
    product_size = Column(String(255), nullable=True, comment='产品规格')
    planter_name = Column(String(255), nullable=True, comment='种植主体名称')
    planter_id = Column(Integer, nullable=True, comment='种植主体id')
    product_area_id = Column(Integer, nullable=True, comment='产区id')
    product_area = Column(String(255), nullable=True, comment='产区')
    memo = Column(String(255), nullable=True, comment='备注')
    plant_plot_id = Column(Integer, nullable=True, comment='种植地块Id')
    plant_plot_name = Column(String(255), nullable=True, comment='种植地块名称')

@event.listens_for(PurchaseDailyPrice, 'after_insert')
def update_historical_records(mapper, connection, target):
    """
    新插入记录时更新历史记录的失效时间
    """
    # 获取当前会话（需确保线程安全）
    session = Session(bind=connection)

    # 更新逻辑：将同一商品的历史记录失效时间设为新记录的生效时间
    session.query(PurchaseDailyPrice).filter(
        PurchaseDailyPrice.product_area_id == target.product_area_id,  # 关联条件
        PurchaseDailyPrice.planter_id == target.planter_id,
        PurchaseDailyPrice.purchase_date == target.purchase_date,
        PurchaseDailyPrice.product_size == target.product_size,
        PurchaseDailyPrice.purchase_price_id != target.purchase_price_id,  # 排除新记录自身
        is_(PurchaseDailyPrice.expiration_time, None)  # 仅更新未失效的记录
    ).update(
        {"expiration_time": target.effective_time},
        synchronize_session=False  # 避免加载对象，提升性能
    )
    session.commit()


